package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;

import ca.ualberta.cs.BibTex.BibTex;

public class ReadingManagement extends EntityRelManagement {
	
	public ReadingManagement(){
		mainTableName = "Reading";
	}

	public void initialize(Statement statement) throws SQLException{
				
		try{
			statement.executeUpdate("DROP TABLE Reading");			
		}catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE Reading ( " +
				"resourceId INTEGER NOT NULL, " +
				"personId INTEGER NOT NULL, " +				
				"date Date, "+ 
				"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
	            	"REFERENCES Person (ID) ON DELETE CASCADE, " +
	            "CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
	                "REFERENCES Resource (ID) ON DELETE CASCADE " +
	            //"CONSTRAINT PK_resource_user PRIMARY KEY (resourceId, personId) " +                        
		") IN CLRSPACE");
		numOfCols = 3;
			
	}
	
	public Integer pageAccessed(BibTex bib, HashMap<String,String> paperInfo, String url, String username){
		Integer resourceId = -1;
		try{
			Connection con = DatabaseController.connectToDB();
			Statement statement = con.createStatement();
			
			resourceId = DatabaseController.resourceMgmt.addOrUpdateResource(bib, paperInfo, url);
			Integer personId = DatabaseController.personsMgmt.getPersonId(username, con);
			
			if(personId == -1 || resourceId == -1){
				statement.close();
				con.commit();
				return resourceId;
			}
			String today = (new java.sql.Date(new Date().getTime())).toString();
			//ResultSet rset = statement.executeQuery("SELECT COUNT(DISTINCT DAY(date)) as ");
			ResultSet rset = statement.executeQuery("SELECT * FROM Reading WHERE resourceId = "+resourceId+" AND personId = "+personId+" " +
															"AND date = '"+ today +"'");
			if(!rset.next()){
				String query = "INSERT INTO Reading (resourceId,personId,date) VALUES " +
					"("+resourceId+","+personId+",'" + today +"'"+")";
			
//			if(resourceId != -1 && personId != -1)
				statement.executeUpdate(query);
			}
			
			statement.close();
			con.commit();
			
		}catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				e = e.getNextException();
			}	   				
			
		} catch (Exception e) {			
			e.printStackTrace();			
		} 
		return resourceId;
	}
	
}
